﻿using Newtonsoft.Json;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml.Linq;
using static Mysqlx.Crud.Order.Types;
using static System.Windows.Forms.AxHost;

namespace JDLT_GIS_DATA
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private new string[] parseName(List<Dictionary<string, double>> items, string name, List<string> notMatch)
        {
            foreach (var item in items)
            {
                foreach (KeyValuePair<string, double> pair in item)
                {
                    //Console.WriteLine($"{pair.Key}: {pair.Value}");
                    string[] key = pair.Key.Split('@')[0].Split('-');
                    string first = key[0];
                    string second = key[1];

                    if (first + "-" + second == name || second + "-" + first == name)
                    {
                        string up = pair.Key.Split('@')[1];
                        string down = pair.Key.Split('@')[2];
                        double realdistance = pair.Value;
                        return new string[] { up, down, realdistance.ToString() }
                        ;
                    }

                }
            }
            notMatch.Add(name);
            return new string[] { "", "", "" };
        }

        /// <summary>
        /// excel数据入库：
        /// json数据和excel数据按元素标识（名称）做匹配，提取json中的上下行和距离信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            //记录未匹配的字段
            List<string> notMatch = new List<string>();
            string jsonString = File.ReadAllText(textBox1.Text);
            // 将json字符串解析成List<Dictionary<string, double>>
            List<Dictionary<string, double>> items = JsonConvert.DeserializeObject<List<Dictionary<string, double>>>(jsonString);
            //存储限速信息
            Dictionary<String, int> speedLimitDic = new Dictionary<string, int>();
            using (StreamReader reader = new StreamReader(textBox3.Text))
            {
                string line;
                while ((line = reader.ReadLine()) != null)
                {
                    String[] lineStrs = line.Split(',');
                    if (lineStrs[0].Equals("XD"))
                    {
                        if (String.IsNullOrEmpty(lineStrs[9]))
                        {
                            speedLimitDic.Add(lineStrs[8], 30);
                        }
                        else
                        {
                            speedLimitDic.Add(lineStrs[8], int.Parse(lineStrs[9]));
                        }
                    }
                }
            }
            // 读取Excel文件
            using (FileStream fs = new FileStream(textBox2.Text, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new XSSFWorkbook(fs);
                ISheet sheet = workbook.GetSheetAt(1);

                // 遍历每一行
                for (int row = 1; row <= sheet.LastRowNum; row++)
                {
                    IRow currentRow = sheet.GetRow(row);
                    if (currentRow != null) // 确保行不为空
                    {
                        ICell currentCell = currentRow.GetCell(0);
                        if (currentCell != null) // 确保单元格不为空
                        {


                            string linenum = currentRow.GetCell(1) == null ? "" : currentRow.GetCell(0).ToString();
                            string graphic_line_num = currentRow.GetCell(1) == null ? "" : currentRow.GetCell(1).ToString();
                            string ele_type = currentRow.GetCell(2) == null ? "" : currentRow.GetCell(2).ToString();
                            string ele_name = currentRow.GetCell(3) == null ? "" : currentRow.GetCell(3).ToString();
                            string up = "";
                            string down = "";
                            string element = currentRow.GetCell(4) == null ? "" : currentRow.GetCell(4).ToString();
                            string direction = currentRow.GetCell(5) == null ? "" : currentRow.GetCell(5).ToString();
                            string startdistance = currentRow.GetCell(6) == null ? "" : currentRow.GetCell(6).ToString();
                            string enddistance = currentRow.GetCell(7) == null ? "" : currentRow.GetCell(7).ToString();
                            string tip = currentRow.GetCell(8) == null ? "" : currentRow.GetCell(8).ToString();
                            string startx = currentRow.GetCell(11) == null ? "" : currentRow.GetCell(11).ToString();
                            string starty = currentRow.GetCell(12) == null ? "" : currentRow.GetCell(12).ToString();
                            string endx = currentRow.GetCell(13) == null ? "" : currentRow.GetCell(13).ToString();
                            string endy = currentRow.GetCell(14) == null ? "" : currentRow.GetCell(14).ToString();
                            int speed = 0;
                            //if (currentRow.GetCell(9) != null && currentRow.GetCell(9).ToString() != "")
                            //{
                            //    speed = int.Parse(currentRow.GetCell(9).ToString());
                            //}
                            if (ele_type.Equals("线段") || ele_type.Equals("道岔线段"))
                            {
                                speed = speedLimitDic[ele_name];
                            }

                            string realdistance = "";
                            //根据ele_name解析up down realdistance
                            if (ele_type == "道岔线段" || ele_type == "线段")
                            {
                                string[] ps = parseName(items, ele_name, notMatch);
                                up = ps[0];
                                down = ps[1];
                                realdistance = ps[2];
                                //打印计算后的距离(10)
                                Console.WriteLine(ele_name+"真实距离：" +realdistance);
                            }

                            // 处理单元格数据入库(10)
                            string sql = "insert into jd_gis_gu_dao_element_base"+textBox4.Text+"(line_num,graphic_line_num,element_type,element_name,element_upele,element_downele,element,direction,start_distance,end_distance,tip,start_x,start_y,end_x,end_y,speedlimit,real_distance) values" +
                                string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},{12},{13},{14},{15},'{16}');",
                                 linenum, graphic_line_num, ele_type, ele_name, up, down, element, direction, startdistance, enddistance, tip, startx, starty, endx, endy, speed, realdistance);
                            MySqlHelper.ExecuteScalar(MySqlHelper.Conn, System.Data.CommandType.Text, sql, null);
                        }
                    }
                }
            }

            //打印输出未匹配的元素
            foreach (string item in notMatch)
            {
                Console.WriteLine(item);
            }
            MessageBox.Show("完成");

        }



        private void txtFiles_DragEnter(object sender, DragEventArgs e)
        {
            // 检查拖动的数据是否是文件
            if (e.Data.GetDataPresent(DataFormats.FileDrop))
            {
                e.Effect = DragDropEffects.Copy; // 允许复制
            }
            else
            {
                e.Effect = DragDropEffects.None; // 不允许其他操作
            }
        }

        private void txtFiles_DragDrop(object sender, DragEventArgs e)
        {
            TextBox tb = sender as TextBox;
            // 获取拖放的文件路径
            string[] files = (string[])e.Data.GetData(DataFormats.FileDrop);

            // 清空 TextBox
            tb.Clear();
            tb.Text = files[0];


        }

        private void button3_Click(object sender, EventArgs e)
        {
            using (FileStream fs = new FileStream(textBox2.Text, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new XSSFWorkbook(fs);
                ISheet sheet = workbook.GetSheetAt(1);
                string json = "{";
                // 遍历每一行
                for (int row = 1; row <= sheet.LastRowNum; row++)
                {
                    IRow currentRow = sheet.GetRow(row);
                    if (currentRow != null) // 确保行不为空
                    {
                        ICell currentCell = currentRow.GetCell(0);
                        if (currentCell != null) // 确保单元格不为空
                        {
                            string ele_type = currentRow.GetCell(2) == null ? "" : currentRow.GetCell(2).ToString();
                            string ele_name = currentRow.GetCell(3) == null ? "" : currentRow.GetCell(3).ToString();
                            string direction = currentRow.GetCell(5) == null ? "" : currentRow.GetCell(5).ToString();
                            //根据ele_name解析up down realdistance
                            if (ele_type == "信号")
                            {
                                json += "\"" + ele_name + "\":";
                                if (direction.Contains("下"))
                                //if (direction.Contains("上"))
                                {
                                    json += "\"" + "X" + "\"";
                                }
                                else
                                {
                                    json += "\"" + "S" + "\"";
                                }
                                json += ",";
                            }

                        }
                    }
                }
                json = json.TrimEnd(',');
                json += "}";
                Console.WriteLine("输出上下行信息："+json);
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            //记录未匹配的字段
            List<string> notMatch = new List<string>();
            string jsonString = File.ReadAllText(textBox1.Text);
            // 将json字符串解析成List<Dictionary<string, double>>
            List<Dictionary<string, double>> items = JsonConvert.DeserializeObject<List<Dictionary<string, double>>>(jsonString);
            //存储限速信息
            Dictionary<string, int> speedLimitDic = new Dictionary<string, int>();
            //存储所有点的像素坐标数据,名称为键，x坐标*y坐标为值
            Dictionary<string, string> allPointCoordsPix = new Dictionary<string, string>();
            //存储所有线段的两端元素，名称为键，x1坐标*y1坐标，x2坐标*y2坐标
            Dictionary<string, string> allLineCoordsPix = new Dictionary<string, string>();
            using (StreamReader reader = new StreamReader(textBox3.Text))
            {
                string line;
                while ((line = reader.ReadLine()) != null)
                {
                    string[] infos_AllArr = line.Split(',');
                    string startValue = infos_AllArr[0];
                    string line_num = "";
                    string graphic_line_num = "";
                    string element_type = "";
                    string element_name = "";
                    string element = "";
                    string direction = "";
                    string start_distance = "";
                    string end_distance = "";
                    string tip = "";
                    int start_x = 0;
                    int start_y = 0;
                    int end_x = 0;
                    int end_y = 0;
                    int speedlimit = 30;
                    string real_distance = "";
                    bool isReturn = false;
                    switch (startValue)
                    {
                        case "Scale":
                        case "SH":
                        case "TI":
                            isReturn = true;
                            speedlimit = 0;
                            break;
                        case "XD":
                            line_num = infos_AllArr[1];
                            if (line_num.StartsWith("M")) element_type = "道岔线段";
                            else element_type = "线段";
                            start_x = int.Parse(infos_AllArr[2].ToString());
                            start_y = int.Parse(infos_AllArr[3].ToString());
                            end_x = int.Parse(infos_AllArr[4].ToString());
                            end_y = int.Parse(infos_AllArr[5].ToString());
                            element_name = infos_AllArr[8];
                            //Console.WriteLine(element_name);
                            if (!string.IsNullOrEmpty(infos_AllArr[9]))speedlimit = int.Parse(infos_AllArr[9].ToString());

                            real_distance = infos_AllArr[10];
                            break;
                        case "SG":
                            line_num = infos_AllArr[1];
                            element_type = "信号";
                            start_x = int.Parse(infos_AllArr[2].ToString());
                            start_y = int.Parse(infos_AllArr[3].ToString());
                            element_name = infos_AllArr[4];
                            speedlimit = 0;
                            break;
                        case "VP":
                            line_num = infos_AllArr[1];
                            element_type = "VP";
                            start_x = int.Parse(infos_AllArr[2].ToString());
                            start_y = int.Parse(infos_AllArr[3].ToString());
                            element_name = infos_AllArr[4];
                            speedlimit = 0;
                            break;
                        case "DC":
                            line_num = infos_AllArr[1];
                            element_type = "道岔";
                            start_x = int.Parse(infos_AllArr[2].ToString());
                            start_y = int.Parse(infos_AllArr[3].ToString());
                            element_name = infos_AllArr[6];
                            speedlimit = 0;
                            break;
                        case "CD":
                            line_num = infos_AllArr[1];
                            element_type = "土挡";
                            start_x = int.Parse(infos_AllArr[2].ToString());
                            start_y = int.Parse(infos_AllArr[3].ToString());
                            element_name = infos_AllArr[5];
                            speedlimit = 0;
                            break;
                        case "GDZD":
                            line_num = infos_AllArr[1];
                            element_type = "轨道电路终端点";
                            start_x = int.Parse(infos_AllArr[2].ToString());
                            start_y = int.Parse(infos_AllArr[3].ToString());
                            element_name = infos_AllArr[5];
                            speedlimit = 0;
                            break;
                        case "JYJ":
                            line_num = infos_AllArr[1];
                            element_type = "绝缘节";
                            start_x = int.Parse(infos_AllArr[2].ToString());
                            start_y = int.Parse(infos_AllArr[3].ToString());
                            element_name = infos_AllArr[5];
                            speedlimit = 0;
                            break;
                        default:
                            isReturn = true;
                            break;
                    }
                    if (isReturn)
                    {
                        continue;
                    }
                    string up = "";
                    string down = "";

                    //根据ele_name解析up down realdistance
                    if (element_type == "道岔线段" || element_type == "线段")
                    {
                        string[] ps = parseName(items, element_name, notMatch);
                        up = ps[0];
                        down = ps[1];
                        real_distance = ps[2];
                        //打印计算后的距离(10)
                        Console.WriteLine(element_name + "真实距离：" + real_distance);
                    }
                    if (startValue.Equals("XD"))
                    {
                        allLineCoordsPix.Add(element_name, start_x + "*" + start_y + "," + end_x + "*" + end_y);
                    }
                    else
                    {
                        if (startValue.Equals("CD") || startValue.Equals("SG") || startValue.Equals("VP") || startValue.Equals("DC") || startValue.Equals("GDZD"))
                        {
                            allPointCoordsPix.Add(element_name, start_x + "*" + start_y);
                        }
                        
                    }
                        // 处理单元格数据入库(10)
                        string sql = "insert into jd_gis_gu_dao_element_base" + textBox4.Text + " (line_num,graphic_line_num,element_type,element_name,element_upele,element_downele,element,direction,start_distance,end_distance,tip,start_x,start_y,end_x,end_y,speedlimit,real_distance) values" +
                            string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},{12},{13},{14},{15},'{16}');",
                             line_num, graphic_line_num, element_type, element_name, up, down, element, direction, start_distance, end_distance, tip, start_x, start_y, end_x, end_y, speedlimit, real_distance);
                    MySqlHelper.ExecuteScalar(MySqlHelper.Conn, System.Data.CommandType.Text, sql, null);
                }
            }
            //反岔子没有添加单独点坐标，所以这里要获取下，根据已经存储的点从线段上判断没有添加的点入库
            int fcIndex = 1;
            //遍历allLineCoordsPix
            foreach (string elementKey in allLineCoordsPix.Keys)
            {
                string graphic_line_num = "";
                string element_type = "道岔";
                string element = "";
                string direction = "";
                string start_distance = "";
                string end_distance = "";
                string tip = "";
                int start_x = 0;
                int start_y = 0;
                int end_x = 0;
                int end_y = 0;
                int speedlimit = 0;
                string up = "";
                string down = "";
                string real_distance = "";
                string[] twoElementsName = elementKey.Split('-');
                if (twoElementsName.Length == 2)
                {
                    //包含第一个点
                    if (allPointCoordsPix.ContainsKey(twoElementsName[0])) 
                    {
                        //两个点都包含，不处理
                        if (allPointCoordsPix.ContainsKey(twoElementsName[1])) continue;
                        else
                        {
                            //如果不包含第二个点
                            //获取存在的第一个点的坐标数据
                            string havePointStr = allPointCoordsPix[twoElementsName[0]];
                            //剔除已有的数据，剩余的为没有的数据
                            string noHavePointStr= allLineCoordsPix[elementKey].Replace(havePointStr, "").Replace(",","");
                            //获取第二个点名称
                            string elementAppendName = twoElementsName[1];
                            //获取第二个点的坐标x和y
                            start_x = int.Parse(noHavePointStr.Split('*')[0]);
                            start_y = int.Parse(noHavePointStr.Split('*')[1]);
                            // 处理单元格数据入库(10)
                            string sql = "insert into jd_gis_gu_dao_element_base" + textBox4.Text + " (line_num,graphic_line_num,element_type,element_name,element_upele,element_downele,element,direction,start_distance,end_distance,tip,start_x,start_y,end_x,end_y,speedlimit,real_distance) values" +
                                string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},{12},{13},{14},{15},'{16}');",
                                 fcIndex, graphic_line_num, element_type, elementAppendName, up, down, element, direction, start_distance, end_distance, tip, start_x, start_y, end_x, end_y, speedlimit, real_distance);
                            MySqlHelper.ExecuteScalar(MySqlHelper.Conn, System.Data.CommandType.Text, sql, null);
                            fcIndex++;
                        }
                    }
                    else
                    {
                        if (allPointCoordsPix.ContainsKey(twoElementsName[1]))
                        {
                            //包含第二个点但是不包含第一个点
                            //获取包含的第二个点坐标数据
                            string havePointStr = allPointCoordsPix[twoElementsName[1]];
                            //剔除已有的数据，剩余的为没有的数据
                            string noHavePointStr = allLineCoordsPix[elementKey].Replace(havePointStr, "").Replace(",", "");
                            //获取第二个点名称
                            string elementAppendName = twoElementsName[0];
                            //获取第二个点的坐标x和y
                            start_x = int.Parse(noHavePointStr.Split('*')[0]);
                            start_y = int.Parse(noHavePointStr.Split('*')[1]);
                            // 处理单元格数据入库(10)
                            string sql = "insert into jd_gis_gu_dao_element_base" + textBox4.Text + " (line_num,graphic_line_num,element_type,element_name,element_upele,element_downele,element,direction,start_distance,end_distance,tip,start_x,start_y,end_x,end_y,speedlimit,real_distance) values" +
                                string.Format("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},{12},{13},{14},{15},'{16}');",
                                 fcIndex, graphic_line_num, element_type, elementAppendName, up, down, element, direction, start_distance, end_distance, tip, start_x, start_y, end_x, end_y, speedlimit, real_distance);
                            MySqlHelper.ExecuteScalar(MySqlHelper.Conn, System.Data.CommandType.Text, sql, null);
                            fcIndex++;
                        }
                        else
                        {
                            //两端都不包含，是异常数据，输出记录
                            Console.WriteLine("异常数据："+ elementKey);
                            continue;
                        }
                    }
                }
                else
                {
                    Console.WriteLine("线段异常，分割后名称不是两个点");
                }
            }
            //打印输出未匹配的元素
            foreach (string item in notMatch)
            {
                Console.WriteLine(item);
            }
            MessageBox.Show("完成");

        }
    }
}
